Note: This section assumes that you are familiar with the Select Expert and that you are reporting off of a SQL database.
Consider the following points when creating a record selection formula:
Any record selection formula that you generate completely with the Select Expert, without writing pieces of the formula yourself, can be pushed down.
This case actually follows from the points below. However, you can write more types of record selection formulas using the tips below than is possible with the Select Expert. To do this, you need to edit the record selection formula directly with the Formula Editor or from within the large text window in the Select Expert.
To open the Record Selection Formula Editor, click the Report menu, point to Edit Selection Formula, and then select Record from the submenu.
Any selection formula that is of the form: DataBaseField SupportedOperator ConstantOrParameterExpression can be pushed down.
Of course DataBaseField is just a database field. SupportedOperator is any of =, <>, <, <=, >, >=, StartsWith, Like or In.
ConstantOrParameterExpression is any expression that involves constant values, operators, functions, and parameter fields. It cannot involve variables, control structures, or fields other than parameter fields. By their very definition, constant and parameter expressions can be evaluated without accessing the database.
Note: A constant or parameter expression can evaluate to a simple value, a range value, an array value, or an array of range values. Here are some examples of such expressions:
{?number parameter} - 3 Year ({?run date}) CurrentDate + 5 DateDiff ("q", CurrentDate, CDate("Jan 1, 1996")) Month (Maximum ({?date range parameter}) + 15) ["Canada", "Mexico", "USA", {?enter a country}] 1000 To 5000 [5000 To 10000, 20000 To 30000, 50000 To 60000]
{Orders.Order Date} >= CurrentDate - 3
The program can also push down an expression that just contains a Boolean field (without the operator and constant parts).
{Orders.Shipped} Not {Orders.Shipped}
IsNull (DataBaseField) can be pushed down.
SqlExpression SupportedOperator ConstantOrParameterExpression can be pushed down.
For example, the selection formula {@ExtendedPrice} > 1000
cannot be pushed down if {@ExtendedPrice} = (Quantity * Price)
. However, if the formula @ExtendedPrice
is replaced with the equivalent SQL Expression, then your record selection formula will be pushed down.
When using multiple expressions that follow the above considerations, separate them with AND and OR operators. You can have several of each, and you can use parentheses to give priority. You can also use NOT. For example:
{Orders.Order ID} < Minimum({?number range}) Or {Orders.Order Amount} >= 1000 (IsNull({Customer.Region}) Or {Customer.Region} = "BC") And {Customer.Last Year's Sales} > 2000
If your record selection formula does not respond as expected, see Troubleshooting record selection formulas.
Seagate Software IMG Holdings, Inc. http://www.seagatesoftware.com Support services: http://support.seagatesoftware.com |